package DBComm;


public class DBExample{
     private java.sql.Connection  con = null;
     private final String url = "jdbc:mysql://";
     private final String serverName= "192.168.0.102"; //Robert's IP 192.168.0.102
     private final String portNumber = "3306"; //MySQL: 3306. SQL Server 2008: 50479
     private final String databaseName= "pIMp";
     private final String userName = "pIMpAdmin";
     private final String password = "ainteasy";
     private final String selectMethod = "cursor"; 
     
     // Constructor
     public DBExample(){}
     
     private String getConnectionUrl(){
          //return url+serverName+":"+portNumber+";databaseName="+databaseName+";selectMethod="+selectMethod+";";
    	 	return url+serverName+":"+portNumber+"/"+databaseName+"?";
     }
     
     private java.sql.Connection getConnection(){
          try{
               //Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
        	   //Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();
        	   Class.forName("com.mysql.jdbc.Driver").newInstance();
               con = java.sql.DriverManager.getConnection(getConnectionUrl(),userName,password);
               if(con!=null) System.out.println("Connection Successful!");
          }catch(Exception e){
               e.printStackTrace();
               System.out.println("Error Trace in getConnection() : " + e.getMessage());
         }
          return con;
      }
     
     public void part1(){
         java.sql.DatabaseMetaData dm = null;
         java.sql.ResultSet rs = null;
         java.sql.Statement st = null;
         String query = "SELECT * FROM client";
         
         try{
             con= this.getConnection();
             //con.createStatement();
             	
         	 //System.out.println("empno \tname \tsalary");
         	
              if(con!=null){      	  
            	   st = con.createStatement();
                   rs = st.executeQuery(query);
                   while(rs.next()){
                        System.out.print(rs.getString(1));
                        System.out.print("\t"+ rs.getString(2));
                        System.out.print("\t"+ rs.getString(3) + "\n");
                        System.out.print("\t"+ rs.getString(4) + "\n");
                        System.out.print("\t"+ rs.getString(5) + "\n");
                   } 
                   System.out.println();

                   rs.close();
                   rs = null;
                   closeConnection();
              }else System.out.println("Error: No active Connection");
         }catch(Exception e){
              e.printStackTrace();
         }
         dm=null;
    }     
     
     public void part2(){
         java.sql.DatabaseMetaData dm = null;
         java.sql.ResultSet rs = null;
         java.sql.Statement st = null;
         String query = "SELECT distinct e.name FROM employee e inner join loan l on e.empno = l.empno, book b " 
         		+ "Where b.isbn = l.isbn "
         		+ "And b.publisher = 'McGraw-Hill' "
         		+ "Group By e.name "
         		+ "Having count(b.isbn) = (Select count(isbn) from book where publisher = 'McGraw-Hill' ) ";
         
         try{
             con= this.getConnection();
             con.createStatement();
             	
             //Title bar
         	 System.out.println("name");
         	
              if(con!=null){      	  
            	   st = con.createStatement();
                   rs = st.executeQuery(query);
                   while(rs.next()){
                        System.out.print(rs.getString(1) + "\n");
                   }
                   System.out.println();

                   rs.close();
                   rs = null;
                   closeConnection();
              }else System.out.println("Error: No active Connection");
         }catch(Exception e){
              e.printStackTrace();
         }
         dm=null;
    }     

     public void part3(){
         java.sql.DatabaseMetaData dm = null;
         java.sql.ResultSet rs = null;
         java.sql.Statement st = null;
         String query = "SELECT e.salary FROM employee e Order By e.salary";
         
         try{
             con= this.getConnection();
             con.createStatement();
             	
             //Title bar
         	 System.out.println("salary");
         	
              if(con!=null){      	  
            	   st = con.createStatement();
                   rs = st.executeQuery(query);
                   while(rs.next()){
                        System.out.print(rs.getString(1) + "\n");
                   } 
                   System.out.println();

                   rs.close();
                   rs = null;
                   closeConnection();
              }else System.out.println("Error: No active Connection");
         }catch(Exception e){
              e.printStackTrace();
         }
         dm=null;
    }     
     
     private void closeConnection(){
          try{
               if(con!=null)
                    con.close();
               con=null;
          }catch(Exception e){
               e.printStackTrace();
          }
     }
     public static void main(String[] args) throws Exception
       {
    	 DBExample myDbTest = new DBExample();
          
          myDbTest.part1();
          //myDbTest.part2();
          //myDbTest.part3();

       }
}


